package sqldb

import (
	_ "github.com/go-sql-driver/mysql"
	"log"
	"time"
	"errors"
	"database/sql"
	"fmt"
)
/**
*	在执行main函数时，设置数据库
*
**/

//存储数据库连接信息
var dbMp map[string]*sql.DB = make(map[string]*sql.DB)
var db *sql.DB
//注册数据库
// 参数说明：
//	dbName：数据库别名
//	dbType:数据库类型，暂支持MYSQL
//	dbURL:数据库连接地址
//	args: 多个参数分别是maxOpenConn(最大连接数),maxIdleConn（最大空闲连接数）,maxConnLiftTime（边接存活时间）
func AddDB(dbName, dbType, dbUrl string, args ...int) error {
	if dbName == "" {
		dbName = "default"
	}
	if dbType == "" {
		dbType = "mysql"
	}
	if dbUrl == "" {
		dbUrl = "root:123456@tcp(127.0.0.1:330)?charset=utf8"
	}
	maxActive := 200
	maxIdle := 100
	maxLifeTime := -1
	for index, arg := range args {
		if index == 0 {
			maxActive = arg
		} else if index == 1 {
			maxIdle = arg
		} else if index == 2 {
			maxLifeTime = arg
		} else {
			break
		}

	}

	sqlDB, err := sql.Open(dbType, dbUrl)
	if err != nil {
		//出错
		log.Println("连接数据库出错:", err.Error())
		return err
	}

	sqlDB.SetMaxOpenConns(maxActive)
	sqlDB.SetMaxIdleConns(maxIdle)
	sqlDB.SetConnMaxLifetime(time.Duration(maxLifeTime) * time.Second)
	err = sqlDB.Ping()
	if err != nil {
		log.Println("Ping数据库失败：", err.Error())
		return err
	}
	dbMp[dbName] = sqlDB
	return nil
}
func UserDefaultDB() bool {
	if dbTemp, ok := dbMp["default"]; ok {
		db = dbTemp
		return ok
	}
	db = nil
	return false
}
//获取数据库
func UseDB(dbStr string) bool {
	if dbTemp, ok := dbMp[dbStr]; ok {
		db = dbTemp
		return ok
	}
	db = nil
	return false
}
//当DB没有连接的时候，打开数据库连接
func openDB() {
	if db == nil {
		db, _ = dbMp["default"]
	}
}

/*
@param: sql 查询语句，select * from table [where col = ?]
@param: args 可变参数，对应SQL语句中的占位符
@return: rowMap 结果集，返回JSON数据 ,err 返回错误
@desc: 查询单条记录
 */
func QueryRowMap(sqlStr string, args ... interface{}) (rowMap RowMap, err error) {
	openDB()
	//判断SQL语句是否为空
	if "" == sqlStr {
		return nil, errors.New("传入的SQL语句不能为空！")
	}
	//调用go-sql-server\Mysql驱动查询
	rows, err := db.Query(sqlStr, args...)
	//db, _ = dbMp["default"]
	if err != nil {
		log.Println("mysql query error", err.Error())
		return nil, err
	}
	//延时关闭Rows
	defer rows.Close()
	//获取记录列
	if columns, err := rows.Columns(); err != nil {
		log.Println(err.Error())
		return nil, err
	} else {
		//拼接记录Map
		values := make([]sql.RawBytes, len(columns))
		scans := make([]interface{}, len(columns))
		for i := range values {
			scans[i] = &values[i]
		}
		for rows.Next() {
			_ = rows.Scan(scans...)
			each := make(RowMap)
			for i, col := range values {
				each[columns[i]] = string(col)
			}
			rowMap = each
			//仅读取第一条记录
			break
		}
		return rowMap, nil
	}
}
/*
@param: sql 查询语句，select * from table [where col = ?]
@param: args 可变参数，对应SQL语句中的占位符
@return: rowMaps 结果集，返回JSON数据 ,err 返回错误
@desc: 查询多条记录
 */
func QueryRowMaps(sqlStr string, args ... interface{}) (rowMaps RowMaps, err error) {
	openDB()
	//判断SQL语句是否为空
	if "" == sqlStr {
		return nil, errors.New("传入的SQL语句不能为空！")
	}
	//调用go-sql-server\Mysql驱动查询
	rows, err := db.Query(sqlStr, args...)
	//db, _ = dbMp["default"]
	if err != nil {
		log.Println("mysql query error", err.Error())
		return nil, err
	}
	//延时关闭Rows
	defer rows.Close()
	//获取记录列
	if columns, err := rows.Columns(); err != nil {
		log.Println(err.Error())
		return nil, err
	} else {
		//拼接记录Map
		values := make([]sql.RawBytes, len(columns))
		scans := make([]interface{}, len(columns))
		for i := range values {
			scans[i] = &values[i]
		}
		for rows.Next() {
			_ = rows.Scan(scans...)
			each := make(RowMap)
			for i, col := range values {
				each[columns[i]] = string(col)
			}
			rowMaps = append(rowMaps, each)
			//仅读取第一条记录
		}
		return rowMaps, nil
	}
}

/*
@param: sql 查询统计语句，select count(1) from table [where col = ?]
@param: args 可变参数，对应SQL语句中的占位符
@return: rowCount 表中符合条件的记录条数
	err 查询过程中出错的信息
@desc: 查询单条记录
 */
func CountTable(sqlStr string, args ...interface{}) (rowCount int64, err error) {
	openDB()
	//判断SQL语句是否为空
	if "" == sqlStr {
		return -1, errors.New("传入的SQL语句不能为空！")
	}

	//调用go-sql-server\Mysql驱动查询
	rows, err := db.Query(sqlStr, args...)
	if err != nil {
		log.Println("mysql query error", err.Error())
		return -1, err
	}
	//延时关闭Rows
	defer rows.Close()

	for rows.Next() {
		var count int64 = 0
		err = rows.Scan(&count)
		if (err != nil) {
			return -1, err
		}
		return count, nil
	}
	return -1, err
}

//更新记录，增加、修改、删除
func executeSql(sqlStr string, args ... interface{}) (rowCount int64, err error) {
	openDB()
	tx, err := db.Begin()
	defer tx.Commit()
	if err != nil {
		log.Println(err.Error())
		return -1, err
	}
	smt, err := tx.Prepare(sqlStr)
	if err != nil {
		log.Println(err.Error())
		return -1, err
	}
	rs, err := smt.Exec(args...)
	if err != nil {
		log.Println(err.Error())
		err = tx.Rollback()
		log.Println(err.Error())
		return -1, err
	}
	rowCount, _ = rs.RowsAffected()
	return rowCount, nil
}

//执行Model
func ExecuteSQLModel(bm SqlModel) (rowCount int64, err error) {
	openDB()
	rowCount = 0
	tx, err := db.Begin()
	defer tx.Commit()
	if err != nil {
		log.Println(err.Error())
		return -1, err
	}
	smt, err := tx.Prepare(string(bm.SQL))
	if err != nil {
		log.Println(err.Error())
		return -1, err
	}
	rs, err := smt.Exec(bm.ARGS...)
	if err != nil {
		log.Println(err.Error())
		err = tx.Rollback()
		log.Println(err.Error())
		return -1, err
	}
	rowCount, _ = rs.RowsAffected()
	return rowCount, nil
}

func ExecuteSQLModels(bms SqlModels) (rowCount int64, err error) {
	openDB()
	rowCount = 0
	tx, err := db.Begin()
	defer tx.Commit()
	if err != nil {
		log.Println(err.Error())
		return -1, err
	}
	for _, bm := range bms {
		rs, err := tx.Exec(string(bm.SQL), bm.ARGS...)
		if err != nil {
			log.Println(err.Error())
			tx.Rollback()
			return -1, err
		}
		rc, _ := rs.RowsAffected()
		rowCount = rowCount + rc
	}
	return rowCount, nil
}
//批量执行SQL模型
func ExecuteSameTableSQLModel(smd SameTableSQLModel) (rowCount int64, err error) {
	openDB()
	rowCount = 0
	tx, err := db.Begin()
	defer tx.Commit()
	if err != nil {
		log.Println(err.Error())
		return -1, err
	}
	smt, err := tx.Prepare(string(smd.SQL))
	if err != nil {
		log.Println(err.Error())
		return -1, err
	}
	for _, args := range smd.B_ARGS {
		rs, err := smt.Exec(args...)
		if err != nil {
			log.Println(err.Error())
			err = tx.Rollback()
			log.Println(err.Error())
			return -1, err
			break
		}
		rc, _ := rs.RowsAffected()
		rowCount += rc
	}
	return rowCount, nil
}

//批量执行不同的操作
func ExecuteSameTableSqlModels(smdS SameTableSQLModels) (rowCount int64, err error) {
	openDB()
	rowCount = 0
	tx, err := db.Begin()
	for _, smd := range smdS {
		smt, err := tx.Prepare(string(smd.SQL))
		if err != nil {
			log.Println(err.Error())
			return -1, err
		}
		for _, args := range smd.B_ARGS {
			rs, err := smt.Exec(args...)
			if err != nil {
				log.Println(err.Error())
				tx.Rollback()
				return -1, err
				break;
			}
			rc, _ := rs.RowsAffected()
			rowCount += rc
		}
	}
	return rowCount, nil
}

/**
* 根据表名称 保存数据
*
**/
func SaveRecord(tableName string, dataMap map[string]interface{}) (rowCount int64, err error) {
	openDB()
	if tableName == "" {
		return -1, errors.New("表名不能为空！")
	}
	dm, err := createSaveSqlModel(tableName, dataMap)
	if err != nil {
		return -1, err
	}
	return ExecuteSQLModel(dm)
}

//根据表名、主键更新数据
func UpdateRecord(tableName, priKey string, dataMap map[string]interface{}) (rowCount int64, err error) {
	openDB()
	if tableName == "" {
		return -1, errors.New("表名不能为空！")
	}
	if priKey == "" {
		return -1, errors.New("主键不能为空！")
	}
	dm, err := createUpdateSqlModel(tableName, priKey, dataMap)
	if err != nil {
		return -1, err
	}
	return ExecuteSQLModel(dm)
}

//根据表名、主键、值删除数据
func DelRecord(tableName, priKey string, value interface{}) (rowCount int64, err error) {
	if tableName == "" {
		return -1, errors.New("表名不能为空！")
	}
	if priKey == "" {
		return -1, errors.New("主键不能为空！")
	}
	sql := fmt.Sprintf("DELETE FROM %s", tableName)
	sql += " WHERE " + priKey + " = ? "
	return executeSql(sql, value)
}

//创建新增SQL模型
func createSaveSqlModel(tableName string, dataMap map[string]interface{}) (dm SqlModel, err error) {
	if tableName == "" {
		return dm, errors.New("表名不能为空！")
	}
	sql := fmt.Sprintf(" INSERT INTO %s ", tableName)
	colSql := "("
	paramsSql := "  VALUES ("

	for key, value := range dataMap {
		colSql += key + ","
		paramsSql += "?,"
		dm.ARGS = append(dm.ARGS, value)
	}
	colSql = colSql[:len(colSql) - 1]
	paramsSql = paramsSql[:len(paramsSql) - 1]

	colSql += ")"
	paramsSql += ")"
	dm.SQL = SqlStr(sql + colSql + paramsSql)
	log.Println(dm.SQL)
	return dm, nil
}
//创建新增SQL模型
func createUpdateSqlModel(tableName, priKey string, dataMap map[string]interface{}) (dm SqlModel, err error) {
	if tableName == "" {
		return dm, errors.New("表名不能为空！")
	}
	if priKey == "" {
		return dm, errors.New("主键不能为空！")
	}
	sql := fmt.Sprintf(" UPDATE  %s ", tableName)
	colSql := " set "
	keyValue, ok := dataMap[priKey]
	if !ok {
		return dm, errors.New("dataMap中不存在主键数据！")
	}
	for key, value := range dataMap {
		colSql += key + " = ?,"
		dm.ARGS = append(dm.ARGS, value)
	}
	colSql = colSql[:len(colSql) - 1]

	colSql += " WHERE " + priKey + " = ? "
	dm.ARGS = append(dm.ARGS, keyValue)
	dm.SQL = SqlStr(sql + colSql)
	log.Println(dm.SQL)
	return dm, nil
}
